#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : ai_price.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-05-22 09:08 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import collections
import numpy as np
import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils import mail_util
from report_system.utils import mailbox_util
from report_system.utils.log_util import log
from report_system.utils.development_util import debug

if debug:
    receivers = "bianzhiwei@iyoujia.com"
    city_mails = mailbox_util.mails_test
else:
    receivers = 'dt@iyoujia.com;yitangf@sweetome.com;yangweiwei@iyoujia.com;zhanglin@iyoujia.com;' \
                'zhangjiaheng@iyoujia.com;liuwenjun@iyoujia.com;songweiyun@iyoujia.com;' \
                'liuguanghua@iyoujia.com;ccsj@iyoujia.com'
    city_mails = mailbox_util.mails_all
log.info('receivers ： ' + receivers)
log.info('city_mails ： ' + str(city_mails))
# 渠道定义 key对应的是业务库渠道ID，values对应只能定价渠道ID
channel_id_dict = {'3': '0', '25': '3', '7': '6'}
channel_name_dict = {'3': '途家', '25': '榛果', '7': '携程酒店'}


def lodge_info():
    lodge_info_dict = dict()
    sql = """select concat(l.id,"") lodge_id,
        l.title 标题,
        city.short_name 城市
        from youjia.lodge l 
        left join youjia.city on city.id = l.city_id
        where city_id <> 376
            """
    df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    for idx, row in df.iterrows():
        lodge_info_dict[row['lodge_id']] = dict(row)
    return lodge_info_dict


def get_youjia_base_price():
    youjia_base_price_dict = dict()
    sql = """select 
        concat(lodge_id,"") lodge_id,
        round(price/100) price
        from youjia.lod_price_base lpb 
        where lpb.sales_channel_id = 4
        group by lodge_id
        """
    df = pd.read_sql_query(sql, db_util.youjia_13_sjz_db)
    for idx, row in df.iterrows():
        youjia_base_price_dict[str(row['lodge_id'])] = row['price']
    return youjia_base_price_dict


youjia_base_price_dict = get_youjia_base_price()
lodge_info_dict = lodge_info()


def get_media_price_df():
    """
    获取中位数价格
    :return:
    """
    sql = """SELECT 
        concat(lodge_id ,"") lodge_id,
        concat(cur_date,"") dt ,
        price 中位数,
        concat(channel_id,"") channel_id
        FROM youjia_report.aip_median_village_price 
        where lodge_id is not null
        and lodge_id <> 0
        and  type = 0
        and cur_date between curdate()
        and date_add(curdate(),interval 20 day)
        ;"""
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def get_youjia_price():
    """
    ['house_id', 'lodge_id', 'dt', 'channel_id', 'channel_name', 'price']
    获取有家的价格
    :return:
    """
    sql = """select 
         
        concat(l.id,"")  lodge_id,

        concat(lps.dt,"") dt,

        concat(lpb.sales_channel_id,"") channel_id,
         
        concat(round(ifnull(lps.price,CASE
                                WHEN  (FIND_IN_SET(DATE_FORMAT(lps.dt,'%%w'),
                                                  (CASE 
                                                   WHEN lpw.weekend_type=1 THEN '5,6,0' 
                                                   WHEN lpw.weekend_type=2 THEN '5,6' 
                                                   WHEN lpw.weekend_type=3 THEN '6,0' 
                                                   ELSE '999' 
                                                   END))>0 AND lpw.price IS NOT NULL AND lpw.price > 0) 
                                THEN lpw.price
                                ELSE lpb.price
                                END ) /100),"") 有家价格    

        from youjia.lod_house lh 
         
        left join youjia_distribution.lodge_mapping_relation lmr on lmr.house_id = lh.id
        left join youjia.lodge l on l.id = lmr.lodge_id 

        left join youjia.lod_price_base lpb on l.id =lpb.lodge_id   
        left join youjia_common.sales_channel sc on sc.id = lpb.sales_channel_id
        
        left join youjia.lod_third_lodge ltl on ltl.lodge_id = l.id and ltl.sales_channel_id =  lpb.sales_channel_id
        left join youjia.lod_price_week lpw on l.id= lpw.lodge_id  and lpb.sales_channel_id = lpw.sales_channel_id
        left join youjia.lod_price_special lps on l.id= lps.lodge_id and lpb.sales_channel_id = lps.sales_channel_id  
                                                                      and lps.is_delele = 0
        where lmr.is_delete = 0
        and l.city_id <> 376
        and lh.city_id <> 376
        
        and lps.dt  between curdate()
        and date_add(curdate(),interval 20 day)
        group by l.id,lpb.sales_channel_id,lps.dt """
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def get_ai_price():
    """
    获取只能定价
    :return:
    """
    sql = """select 
        concat(lodge_id,"") lodge_id,
        concat(cur_date,"") dt, 
        concat(channel_id,"") channel_id,
        price 智能定价
        from youjia_report.aip_median_village_price 
        where cur_date between curdate() 
        and date_add(curdate(),interval 20 day)
        and lodge_id is not null
        and lodge_id <> 0 """
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def get_lodge_channel():
    """
    智能定价的房源表 开关 0 关闭，1 开启
    :return:
    """
    sql = """SELECT concat(pl.lodge_id,"") lodge_id,
            concat(pl.channel_id,"") channel_id,
            city.short_name 城市
            FROM youjia.pricing_lodge pl 
            left join youjia.city on city.id = pl.city_id
            where state = 1
            and city.id <> 376
            """
    return pd.read_sql_query(sql, db_util.youjia_13_sjz_db)


def format_df(df):
    # lodge_info_dict  youjia_base_price_dict
    lodge_ids = df.lodge_id.unique()
    df_list = list()
    for lodge_id in lodge_ids:
        lodge_dict = dict()
        price = youjia_base_price_dict[str(lodge_id)] if str(lodge_id) in youjia_base_price_dict else '-'
        lodge_dict['基础信息'] = {'房源id': str(lodge_id), '标题': lodge_info_dict[lodge_id]['标题'],
                              '城市': lodge_info_dict[lodge_id]['城市']}
        lodge_df = df[df.lodge_id == lodge_id]
        for idx, row in lodge_df.iterrows():
            row_dt = str(row['dt'])
            if row_dt != '' and row_dt != np.nan:
                lodge_dict[row_dt] = {'有家价格': row['有家价格'], '中位数': row['中位数'], '智能定价': row['智能定价']}
        for i in range(0, -20, -1):
            dt = str(date_util.curdate(i))
            if dt not in lodge_dict:
                lodge_dict[dt] = {'有家价格': price, '中位数': '-', '智能定价': '-'}
        df_list.append(lodge_dict)
    last_list = list()
    for df_row in df_list:

        a_dict = collections.OrderedDict()
        a_dict.update(df_row['基础信息'])
        for i in range(0, -20, -1):
            dt = str(date_util.curdate(i))
            dt_row = df_row[dt]
            a_dict.update({dt + ' 有家价格': dt_row['有家价格']})
            a_dict.update({dt + ' 中位数': dt_row['中位数']})
            a_dict.update({dt + ' 智能定价': dt_row['智能定价']})
        last_list.append(a_dict)

    return pd.DataFrame(last_list)


def process():
    lodge_channel_df = get_lodge_channel()

    youjia_price_df = get_youjia_price()
    media_price_df = get_media_price_df()
    ai_price_df = get_ai_price()
    df_dict = dict()
    # df_dict['中位数'] = media_price_df
    # df_dict['智能定价'] = ai_price_df

    for youjia_channel_id, ai_channel_id in channel_id_dict.items():
        channel_name = channel_name_dict[youjia_channel_id]
        log.info("youjia_channel_id = " + str(youjia_channel_id))
        log.info("ai_channel_id = " + str(ai_channel_id))
        log.info("channel_name = " + str(channel_name))
        # 获取该渠道开启智能定价的所有房源
        lodge_df = lodge_channel_df[lodge_channel_df.channel_id == ai_channel_id]
        log.info("lodge_df = " + str(lodge_df.shape))
        # pd.Series.dtype

        # 获取 有家 该渠道 所有房源的价格
        youjia_df = youjia_price_df[youjia_price_df.channel_id == youjia_channel_id]
        log.info("youjia_df = " + str(youjia_df.shape))

        # 获取   该渠道 所有房源的 中位数
        channel_media_price_df = media_price_df[media_price_df.channel_id == ai_channel_id]
        log.info("channel_media_price_df = " + str(channel_media_price_df.shape))

        # 获取   该渠道 所有房源的 智能定价
        channel_ai_price_df = ai_price_df[ai_price_df.channel_id == ai_channel_id]
        log.info("channel_ai_price_df = " + str(channel_ai_price_df.shape))

        # df = pd.merge(df, channel_media_price_df)
        df = pd.merge(lodge_df, youjia_df, how='left', on='lodge_id')

        df = pd.merge(df, channel_media_price_df, how='left', on=['lodge_id', 'dt'])
        df = pd.merge(df, channel_ai_price_df, how='left', on=['lodge_id', 'dt'])
        log.info("最终 df = " + str(df.shape))
        # df_dict[channel_name] = df
        df_dict[channel_name] = format_df(df)
    # file_path = excel_util.pd_to_excel(df_dict, '中位数', engine="xlsxwriter", na_rep='-', index=False)
    text = """    Dear All:
            附件是智能定价！内部包含途家、榛果、携程数据！
            由于榛果、携程在智能定价过程出现问题，
            故目前无法提供准确智能定价价格 与 中位数
    如有问题！
    请联系: dt@iyoujia.com
    """

    mail_util.distribute_mail(df_dict=df_dict, file_name='智能定价', text=text, engine="xlsxwriter", index=False,
                              title="【智能定价】", receiver=receivers, city_mails=city_mails, city=True, city_key='城市')


def run():
    try:
        process()
    except Exception as e:
        mail_util.error_mail("智能定价-报表", e.__str__())
        log.exception(e)


if __name__ == '__main__':
    run()
